package com.css.fxfzxczh.modules.buildingKilometerGrid.repository;

import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSONObject;
import com.css.fxfzxczh.modules.buildingKilometerGrid.model.entity.FxqhBuildingGridDataVersion;
import com.css.fxfzxczh.modules.buildingKilometerGrid.model.entity.FxqhBuildingGridUrbanWood;
import com.css.fxfzxczh.modules.buildingKilometerGrid.model.param.FxqhBuildingGridDataVersionPageParam;
import com.css.fxfzxczh.modules.buildingKilometerGrid.model.param.QueryByVersionParam;
import com.css.fxfzxczh.modules.buildingKilometerGrid.model.param.VersionSaveMetaDataParam;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * 数据版本
 */
@Repository
@Slf4j
public class FxqhBuildingGridVersionRepository {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 插入
     *
     * @param fxqhBuildingGridDataVersion 版本实体
     */
    public void insert(FxqhBuildingGridDataVersion fxqhBuildingGridDataVersion) {
        String sql =
                "INSERT INTO fxqh_building_grid_data_version (id, version_code,version_time,version_desc,attachment_ids,upload_user,update_desc,data_range_country,data_range_province,data_range_city,data_name,data_desc,upload_time,del_flag,data_file_ids,data_range_area,upload_source,spatial_data_type,projection_info,field_num,field_entry,update_time,update_user,data_range_str,create_time,create_user) VALUES(:id, :versionCode,:versionTime,:versionDesc,:attachmentIds,:uploadUser,:updateDesc,:dataRangeCountry,:dataRangeProvince,:dataRangeCity,:dataName,:dataDesc,:uploadTime,:delFlag,:dataFileIds,:dataRangeArea,:uploadSource,:spatialDataType,:projectionInfo,:fieldNum,:fieldEntry,:updateTime,:updateUser,:dataRangeStr,:createTime,:createUser)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(fxqhBuildingGridDataVersion);
        namedParameterJdbcTemplate.update(sql, source);
        log.debug("sys_data_version insert {}", JSONObject.toJSON(fxqhBuildingGridDataVersion));
    }

    /**
     * 查询历史版本号
     *
     * @return
     */
    public List<String> getVersionCodeList(String dataType) {
        String sql = "select version_code from fxqh_building_grid_data_version where del_flag = '0' and data_type = '" + dataType + "' order by create_time desc";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    /**
     * 删除旧版本
     *
     * @param versionCode
     * @param delUid
     */
    public void delByVersionCode(String versionCode, Date delTime, String delUid) {
        String sql = "update fxqh_building_grid_data_version set del_flag = '1', update_time = '" + DateUtil.format(delTime, "yyyy-MM-dd HH:mm:ss") + "', update_user = '" + delUid + "' where version_code = '" + versionCode + "' and del_flag = '0';";
        jdbcTemplate.update(sql);
    }

    public List<FxqhBuildingGridDataVersion> selectByIds(List<String> ids) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT * from fxqh_building_grid_data_version where id IN ('");
        for (String id : ids) {
            sqlBuilder.append(id).append("', '");
        }
        sqlBuilder.append("') order by create_time desc");
        return jdbcTemplate.query(sqlBuilder.toString().replaceAll("\"", ""), new BeanPropertyRowMapper<>(FxqhBuildingGridDataVersion.class));
    }

    /**
     * 根据id删除
     *
     * @param ids
     * @param delUid
     * @param delTime
     */
    public void delByIds(List<String> ids, String delUid, Date delTime) {
        String sql = "update fxqh_building_grid_data_version set del_flag = '1', update_time = '" + DateUtil.format(delTime, "yyyy-MM-dd HH:mm:ss") + "', update_user = '" + delUid + "' where id in ('" + StringUtils.join(ids, "','") + "');";
        jdbcTemplate.update(sql);
    }

    public List<String> selectList(FxqhBuildingGridDataVersionPageParam param) {
        StringBuilder sqlBuilder = new StringBuilder("select t.id from fxqh_building_grid_data_version t where t.del_flag = '0' ");
        selectListSQLBuilder(param, sqlBuilder);
        sqlBuilder.append(" order by t.create_time desc");
        if (param.getPage() != null && param.getRows() != null) {
            this.pageSQLAppend(sqlBuilder, param.getPage(), param.getRows());
        }
        return jdbcTemplate.queryForList(sqlBuilder.toString(), String.class);
    }

    private void pageSQLAppend(StringBuilder sqlBuilder, int curr, int size) {
        sqlBuilder.append(" limit ").append(size).append(" offset ").append(curr);
    }

    /**
     * 根据查询参数构建分页查询sql的
     *
     * @param param
     * @param sqlBuilder
     */
    private void selectListSQLBuilder(FxqhBuildingGridDataVersionPageParam param, StringBuilder sqlBuilder) {
        if (param.getVersionStartTime() != null && param.getVersionEndTime() != null) {
            sqlBuilder.append(" and t.version_time between '").append(DateUtil.format(param.getVersionStartTime(), "yyyy-MM-dd")).append("' and '").append(DateUtil.format(param.getVersionEndTime(), "yyyy-MM-dd")).append("' ");
        }
        if (param.getUploadStartTime() != null && param.getUploadEndTime() != null) {
            sqlBuilder.append(" and t.upload_time between '").append(DateUtil.format(param.getUploadStartTime(), "yyyy-MM-dd")).append("' and '").append(DateUtil.format(param.getUploadEndTime(), "yyyy-MM-dd")).append("' ");
        }
//        if (StringUtils.isNotBlank(param.getDataType())) {
//            sqlBuilder.append(" and t.data_type ='").append(param.getDataType()).append("'");
//        }
        if (StringUtils.isNotBlank(param.getVersionCode())) {
            sqlBuilder.append(" and t.version_code like '%").append(param.getVersionCode().trim()).append("%' ");
        }
        if (StringUtils.isNotBlank(param.getDataName())) {
            sqlBuilder.append(" and t.data_name like '%").append(param.getDataName().trim()).append("%' ");
        }
        if (param.getDataRangeCountry() != null) {
            sqlBuilder.append(" and t.data_range_country = '").append(param.getDataRangeCountry()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getDataRangeProvince())) {
            if (param.getDataRangeProvince().equals("-1")) {
                sqlBuilder.append(" and t.data_range_province is null ");
            } else {
                if (param.getHasNationwide() == 0) {
                    sqlBuilder.append(" and t.data_range_province = '").append(param.getDataRangeProvince()).append("' ");
                } else {
                    sqlBuilder.append(" and (data_range_province = '").append(param.getDataRangeProvince()).append("' ").append(" or data_range_province is null ").append(") ");
                }
            }
        }
        if (StringUtils.isNotBlank(param.getDataRangeCity())) {
            if (param.getHasNationwide() == 0) {
                sqlBuilder.append(" and t.data_range_city = '").append(param.getDataRangeCity()).append("' ");
            } else {
                sqlBuilder.append(" and (data_range_city = '").append(param.getDataRangeCity()).append("' ").append(" or data_range_city is null ").append(") ");
            }
        }
        if (StringUtils.isNotBlank(param.getDataRangeArea())) {
            if (param.getHasNationwide() == 0) {
                sqlBuilder.append(" and t.data_range_area = '").append(param.getDataRangeArea()).append("' ");
            } else {
                sqlBuilder.append(" and (data_range_area = '").append(param.getDataRangeArea()).append("' ").append(" or data_range_area is null ").append(") ");
            }
        }
        if (StringUtils.isNotBlank(param.getVersionDesc())) {
            sqlBuilder.append(" and t.version_desc like '%").append(param.getVersionDesc().trim()).append("%' ");
        }
        if (StringUtils.isNotBlank(param.getUploadUserIds())) {
            sqlBuilder.append(" and t.upload_user in ('").append(param.getUploadUserIds()).append("')");
        }
    }

    /**
     * 统计
     *
     * @param param
     * @return
     */
    public Integer selectCount(FxqhBuildingGridDataVersionPageParam param) {
        StringBuilder sqlBuilder = new StringBuilder("select count(*) from fxqh_building_grid_data_version t where t.del_flag = '0' ");
        this.selectListSQLBuilder(param, sqlBuilder);
        return jdbcTemplate.queryForObject(sqlBuilder.toString(), Integer.class);
    }

    /**
     * 查询全部上传人
     *
     * @return
     */
    public List<String> selectAllUploadUserId() {
        return jdbcTemplate.queryForList("SELECT upload_user from fxqh_building_grid_data_version GROUP BY upload_user;", String.class);
    }

    /**
     * 根据版本号查询
     *
     * @param versionCode
     * @return
     */
    public FxqhBuildingGridDataVersion selectByVersionCode(String versionCode) {
        String sql = "select * from fxqh_building_grid_data_version where version_code = '" + versionCode + "' and del_flag = '0' limit 1 offset 0";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(FxqhBuildingGridDataVersion.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 保存元数据信息
     *
     * @param param
     */
    public void saveMetaDataInfo(VersionSaveMetaDataParam param) {
        String sql = "update fxqh_building_grid_data_version set " +
                "upload_source = :uploadSource," +
                "spatial_data_type = :spatialDataType," +
                "projection_info = :projectionInfo," +
                "field_num = :fieldNum," +
                "field_entry = :fieldEntry," +
                "update_time = :updateTime," +
                "update_user = :updateUser" +
                " where version_code" + " = :versionCode and del_flag = '0'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    /**
     * 根据id查询
     *
     * @param id
     * @return
     */
    public FxqhBuildingGridDataVersion selectById(String id) {
        String sql = "select * from fxqh_building_grid_data_version where id = '" + id + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(FxqhBuildingGridDataVersion.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<FxqhBuildingGridDataVersion> listByDataVersion(QueryByVersionParam param) {
        String sql = "select *,ST_AsText(geom) geom from fxqh_building_grid_data_version where del_flag = '0' and data_version = '" + param.getVersionCode() + "'";
        sql = queryByDataVersionSQLBuild(param, sql);
        sql += " order by create_time desc ";
        if (param.getPage() != null && param.getRows() != null) {
            sql += (" limit " + param.getRows() + " offset " + param.getRows() * (Math.max(param.getPage(), 1) - 1));
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FxqhBuildingGridDataVersion.class));
    }

    private static String queryByDataVersionSQLBuild(QueryByVersionParam queryByVersionParam, String sql) {
        if (StringUtils.isNotBlank(queryByVersionParam.getProvince())) {
            sql += " and province = '" + queryByVersionParam.getProvince() + "' ";
        }
        if (StringUtils.isNotBlank(queryByVersionParam.getCity())) {
            sql += " and city = '" + queryByVersionParam.getCity() + "' ";
        }
        if (StringUtils.isNotBlank(queryByVersionParam.getCounty())) {
            sql += " and county = '" + queryByVersionParam.getCounty() + "' ";
        }
//        if (StringUtils.isNotBlank(queryByVersionParam.getKeyword())) {
//            sql += " and (jb_code ='" + queryByVersionParam.getKeyword() + "' or sj_name like'%" + queryByVersionParam.getKeyword() + "%' or province like'%" + queryByVersionParam.getKeyword() + "%' or city like'%" + queryByVersionParam.getKeyword() + "%' or county like'%" + queryByVersionParam.getKeyword() + "%' or town like'%" + queryByVersionParam.getKeyword() + "%' )";
//        }
        return sql;
    }

    public Long countByDataVersion(QueryByVersionParam param) {
        String sql = "select count(*) from fxqh_building_grid_data_version where del_flag = '0' and data_version = '" + param.getVersionCode() + "'";
        sql = queryByDataVersionSQLBuild(param, sql);
        return jdbcTemplate.queryForObject(sql, Long.class);
    }
}
